package com.tlkj.common.curd.util;

import cn.hutool.core.util.StrUtil;
import com.tlkj.common.curd.entity.ApiParams;
import com.tlkj.common.curd.exception.OpenApiException;
import org.apache.ibatis.builder.xml.XMLMapperBuilder;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import java.io.ByteArrayInputStream;
import java.io.StringReader;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author flyong86
 */
@Component
public class DynamicSqlFieldExtractor {

    private static final Logger log = LoggerFactory.getLogger(DynamicSqlFieldExtractor.class);
    @Autowired
    private SqlSession sqlSession;

    @Transactional
    public List<ApiParams> getDynamicSqlField(String dynamicSql, Map<String, Object> params) throws Exception {
        // 获取 MyBatis 的配置对象
        Configuration configuration = sqlSession.getConfiguration();

        String mapperName = "dynamicSqlMapper" + System.currentTimeMillis();

        try {
            // 包装成一个完整的 mapper 文件内容
            String completeMapper = "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n" +
                    "<!DOCTYPE mapper PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\" \"http://mybatis.org/dtd/mybatis-3-mapper.dtd\">\n" +
                    "<mapper namespace=\"" + mapperName + "\">\n" +
                    "  <select id=\"dynamicQuery\">\n" + dynamicSql + "\n  </select>\n" +
                    "</mapper>";

            XMLMapperBuilder mapperBuilder = new XMLMapperBuilder(
                    new ByteArrayInputStream(completeMapper.getBytes(StandardCharsets.UTF_8)),
                    configuration,
                    mapperName + ".xml",
                    configuration.getSqlFragments()
            );

            mapperBuilder.parse();  // 解析并生成 MappedStatement

        } catch (Exception e) {
            log.error("SQL脚本解析失败!", e);
            throw new OpenApiException("SQL脚本解析失败!");
        }

        // 执行查询返回字段
        List<ApiParams> returnParams = new ArrayList<>();
        try {
            // 从 configuration 中获取 SqlSource
            MappedStatement mappedStatement = configuration.getMappedStatement(mapperName + ".dynamicQuery");
            BoundSql boundSql = mappedStatement.getBoundSql(params);

            // 获取数据库连接
            Connection conn = sqlSession.getConnection();
            DatabaseMetaData databaseMetaData = conn.getMetaData();
            String productName = databaseMetaData.getDatabaseProductName();
            PreparedStatement pstmt = conn.prepareStatement(boundSql.getSql());

            // 设置请求参数
            setParameters(pstmt, boundSql);
            try (ResultSet rs = pstmt.executeQuery()) {
                // 获取 ResultSetMetaData
                ResultSetMetaData metaData = rs.getMetaData();
                // 输出列名
                int columnCount = metaData.getColumnCount();
                for (int i = 1; i <= columnCount; i++) {

                    ApiParams apiParams = new ApiParams();
                    apiParams.setName(StrUtil.toCamelCase(metaData.getColumnName(i)));
                    apiParams.setField(metaData.getColumnName(i));
                    apiParams.setParamType(ApiParams.ParamType.RETURN.getVal());
                    apiParams.setDataType(mapToType(metaData.getColumnClassName(i)));

                    String desc = getColumnDescription(conn, productName, metaData.getTableName(i), metaData.getColumnName(i));
                    apiParams.setDesc(desc);
                    returnParams.add(apiParams);
                }

            }
        } catch (Exception e) {
            log.error("获取返回字段信息失败，请检测SQL脚本是否正确!", e);
            throw new OpenApiException("获取返回字段信息失败, 请检测SQL脚本是否正确!");
        } finally {
            configuration.getMappedStatementNames().remove(mapperName + ".dynamicQuery");
        }

        return returnParams;
    }

    // 设置 PreparedStatement 参数
    private static void setParameters(PreparedStatement pstmt, BoundSql boundSql) throws SQLException {
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        Map<String, Object> parameterMap = (Map<String, Object>) boundSql.getParameterObject();
        for (int i = 0; i < parameterMappings.size(); i++) {
            ParameterMapping parameterMapping = parameterMappings.get(i);
            String propertyName = parameterMapping.getProperty();
//            Object value = getProperty(parameterObject, propertyName);
            pstmt.setObject(i + 1, parameterMap.get(propertyName));
        }
    }


    // 获取字段描述信息
    private static String getColumnDescription(Connection conn,String dbType, String tableName, String columnName) throws SQLException {
        try {
            if (dbType.toLowerCase().contains("mysql")) {
                String sql = "SELECT COLUMN_COMMENT " +
                        "FROM INFORMATION_SCHEMA.COLUMNS " +
                        "WHERE TABLE_NAME = '" + tableName + "' " +
                        "AND COLUMN_NAME = '" + columnName + "'";
                try (Statement stmt = conn.createStatement();
                     ResultSet rs = stmt.executeQuery(sql)) {
                    if (rs.next()) {
                        return rs.getString("COLUMN_COMMENT");
                    }
                }
            }
        } catch (Exception e) {
            // 不需要处理异常
            log.error("无法获取字段描述!", e);
        }
        return null;
    }


    // 根据 JDBC 数据类型映射为 参数 类型
    private static String mapToType(String columnClassName) {
        if (columnClassName.startsWith("java.lang.Integer") ||
                columnClassName.startsWith("java.lang.Long") ||
                columnClassName.startsWith("java.lang.Short") ||
                columnClassName.startsWith("java.math.BigInteger")) {
            return "Integer";
        } else if (columnClassName.startsWith("java.lang.Float") ||
                columnClassName.startsWith("java.lang.Double") ||
                columnClassName.startsWith("java.math.BigDecimal")) {
            return "Float";
        } else if (columnClassName.startsWith("java.sql.Date") ||
                columnClassName.startsWith("java.sql.Time") ||
                columnClassName.startsWith("java.sql.Timestamp") ||
                columnClassName.startsWith("java.time.LocalDate") ||
                columnClassName.startsWith("java.time.LocalTime") ||
                columnClassName.startsWith("java.time.LocalDateTime")) {
            return "DateTime";
        } else if (columnClassName.startsWith("java.lang.Boolean")) {
            return "Boolean";
        } else {
            return "String";
        }
    }

}
